package com.utils;


import com.csvreader.CsvReader;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Workbook;

import java.io.File;
import java.nio.charset.Charset;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Slf4j
public class GmdbCsvDateUtils {





        public static Map<String,List<Map<String, Object>>> importExcel(String filePath) throws Exception {
            Map<String,List<Map<String, Object>>> returnMap=new HashMap<>(8);
            File files = new File(filePath);
            String dburl="jdbc:mysql://localhost:3228/FRD?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true";
            String dbUserName="root";
            String psd="Sinobase@123";


            for (File file : files.listFiles()) {
                Workbook wb = null;
                // 读取上传文件(excel)的名字，含后缀后
                String fileName = file.getName();
                if (!fileName.endsWith("csv")) {
                    continue;
                }
                fileName=StringUtils.lowerCase(fileName.substring(0,fileName.indexOf(".csv")));
                fileName = fileName.split("gmdb_")[1].replaceAll("_", " ");
                List<String> strings = MysqlDbUtil.showTables(dburl, dbUserName, psd);
                Map<String,String> tableNameMap=new HashMap<>();
                for (String string : strings) {
                    tableNameMap.put(StringUtils.lowerCase(string),string);
                }

                switch (fileName) {
                    case "china local industry list":
                        fileName= "local industry list";
                        break;
                    case "china top account candidates":
                        fileName="top account candidates";
                        break;
                    case "emc sfdc contact mf":
                        fileName="emc sfdc contact";
                        break;
                }
                if(!tableNameMap.containsKey(fileName)){
                    log.info("文件 {}  wei找到与之对应数据库",fileName);
                    continue;
                }

                String tableName=tableNameMap.get(fileName);
                final List<String> columnList = MysqlDbUtil.getColumnList(dburl, dbUserName, psd, tableName);
                CsvReader csvReader = new CsvReader(file.getPath(), ',', Charset.forName("gbk"));
               List<Map<String,Object>> dataList=new ArrayList<>();
                while (csvReader.readRecord()) {
                    Map<String,Object> map=new HashMap<>(8);
                    for (int i = 0; i < columnList.size(); i++) {
                        String s = csvReader.get(i);
                        map.put(columnList.get(i), s);
                        if("DELL_IDC_TID_DESC_CN".equals(columnList.get(i))){
                            log.info(s);
                        }
                    }
                    dataList.add(map);
                }
                returnMap.put(tableName,dataList);
                // 根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            }

            return returnMap;
        }




    private static Object getValue(Row row, int k) {
        Cell cell2 = row.getCell(k);
        Object obj = null;
        if (cell2 != null) { // getCell 获取单元格数据
            switch (cell2.getCellType()) {
                case BOOLEAN:
                    obj = cell2.getBooleanCellValue();
                    break;
                case NUMERIC:
                    Double numericCellValue = cell2.getNumericCellValue();
                    obj = numericCellValue.intValue();
                    break;
                case STRING:
                    obj = cell2.getStringCellValue();
                    break;
                case BLANK:
                    break;
                case ERROR:
                    obj = cell2.getErrorCellValue();
                    break;
            }
        } else {
            return null ;
        }

        return obj;
    }
}
